package etl.db;

import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import org.apache.commons.collections.map.HashedMap;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.JdbcUtils;

import com.mchange.v2.c3p0.ComboPooledDataSource;


/**
 * 验证一些API调用
 * @author taowei20061122@163.com
 *
 */
public class TestDB {
	ComboPooledDataSource dataSource = new ComboPooledDataSource();

	JdbcTemplate jdbcTemplate;
	
	public static Object getResultSetValue(ResultSet rs, int index) throws SQLException {
		Object obj = rs.getObject(index);
		String className = null;
		if (obj != null) {
			className = obj.getClass().getName();
		}
		if (obj instanceof Blob) {
			obj = rs.getBytes(index);
		}
		else if (obj instanceof Clob) {
			obj = rs.getString(index);
		}
		else if (className != null &&
				("oracle.sql.TIMESTAMP".equals(className) ||
				"oracle.sql.TIMESTAMPTZ".equals(className))) {
			obj = rs.getTimestamp(index);
		}
		else if (className != null && className.startsWith("oracle.sql.DATE")) {
			String metaDataClassName = rs.getMetaData().getColumnClassName(index);
			if ("java.sql.Timestamp".equals(metaDataClassName) ||
					"oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
				obj = rs.getTimestamp(index);
			}
			else {
				obj = rs.getDate(index);
			}
		}
		else if (obj != null && obj instanceof java.sql.Date) {
			if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
				obj = rs.getTimestamp(index);
			}
		}
		return obj;
	}
	
	public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		Map<String, Object> mapOfColValues = new HashedMap(columnCount);
		for (int i = 1; i <= columnCount; i++) {
			String key = rsmd.getColumnLabel(i);
			//获取最合适的值
			Object obj = getResultSetValue(rs, i);
			mapOfColValues.put(key, obj);
		}
		return mapOfColValues;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		// TODO Auto-generated method stub
		TestDB testDB = new TestDB();
		/*
		 *  <property name="driverClass" value="${c3p0.driverClassName}"/>
	        <property name="jdbcUrl" value="${c3p0.url}"/>
	        <property name="user" value="${c3p0.username}"/>
	        <property name="password" value="${c3p0.password}"/>
	        <property name="initialPoolSize" value="10"/>
	        <property name="maxPoolSize" value="20"/>
	        <property name="minPoolSize" value="10"/>
	        <property name="acquireIncrement" value="1"/>
	        <property name="acquireRetryAttempts" value="10"/>
		 */
		testDB.dataSource.setDriverClass("oracle.jdbc.OracleDriver");
		testDB.dataSource.setJdbcUrl("jdbc:oracle:thin:@localhost:1521:orcl");
		testDB.dataSource.setUser("emcd");
		testDB.dataSource.setPassword("emcd");
		testDB.dataSource.setInitialPoolSize(10);
		testDB.dataSource.setMaxPoolSize(20);
		testDB.dataSource.setMinPoolSize(10);
		testDB.dataSource.setAcquireIncrement(1);
		testDB.dataSource.setAcquireRetryAttempts(10);
		
		testDB.jdbcTemplate = new JdbcTemplate(testDB.dataSource);
		Connection connection = testDB.dataSource.getConnection();
		
		Statement stmt = connection.createStatement();
		
		ResultSet rs = stmt.executeQuery("select id,createuser,createtime as t,time_stamp as ts,clobs,blobs from test");
		
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		while (rs.next()) {
			for (int index = 1; index <= columnCount; index++) {
				String columnName = rsmd.getColumnLabel(index);
				
				Object obj = rs.getObject(index);
				
				String className = null;
				if (obj != null) {
					className = obj.getClass().getName();
				}
				if (obj instanceof Blob) {
					obj = rs.getBytes(index);
				}
				else if (obj instanceof Clob) {
					obj = rs.getString(index);
				}
				else if (className != null &&
						("oracle.sql.TIMESTAMP".equals(className) ||
						"oracle.sql.TIMESTAMPTZ".equals(className))) {
					obj = rs.getTimestamp(index);
				}
				else if (className != null && className.startsWith("oracle.sql.DATE")) {//什么情况下有这种情况？待验证
					String metaDataClassName = rs.getMetaData().getColumnClassName(index);
					if ("java.sql.Timestamp".equals(metaDataClassName) ||
							"oracle.sql.TIMESTAMP".equals(metaDataClassName)) {
						obj = rs.getTimestamp(index);
					}
					else {
						obj = rs.getDate(index);
					}
				}
				else if (obj != null && obj instanceof java.sql.Date) {
					if ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index))) {
						obj = rs.getTimestamp(index);
					}
				}
				/*
				 * Oracle 10g:
				 * 
				 * NUMBER: java.math.BigDecimal
				 * DATE: java.sql.Timestamp
				 * VARCHAR2: java.lang.String
				 * TIMESTAMP(6) WITH LOCAL TIME ZONE: oracle.sql.TIMESTAMPLTZ
				 * CLOB: oracle.sql.CLOB
				 * BLOB: oracle.sql.BLOB
				 * 
				 * 
				 */
				if (className != null) {
					System.out.println("columnName="+columnName+"--className="+className);
					String metaDataClassName = rs.getMetaData().getColumnClassName(index);
					System.out.println("metaDataClassName=========>"+metaDataClassName);
				}
				
			}
		}
		
		System.out.println("==================================================================");
		
		rs = stmt.executeQuery("select id,createuser,createtime as t,time_stamp as ts,clobs,blobs from test");
		int rowNum = 0;
		while (rs.next()) {
			Map rowDataMap = (Map)testDB.mapRow(rs, rowNum++);
			System.out.println(rowDataMap);
		}

	}

}
